package itsm.isperp.codegenerator;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.StopWatch;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * 
 * 
 * 
 * @author lizx
 * @date 2015-2-2
 * @since 1.0
 * 
 */
public class AlterChatset {

	String host = "localhost";

	String dbName = "isperp_eos";

	@Test
	public void getAlterSql() {

		JdbcTemplate jdbctemplate = getJdbctemplate();

		String sql = "SELECT table_name,table_rows,table_collation,create_time,update_time,table_comment FROM information_schema.tables  WHERE table_type = 'BASE TABLE' AND table_schema='"
				+ dbName + "'  ";

		List<Map<String, Object>> tables = jdbctemplate.queryForList(sql);

		List<String> sqls = new ArrayList<>();
		List<String> sqls2 = new ArrayList<>();

		for (Map<String, Object> m : tables) {

			String tableName = (String) m.get("table_name");

			if (tableName.startsWith("act_")) {
				continue;
			}
			String table_collation = (String) m.get("table_collation");
			if (!"utf8_general_ci".equals(table_collation)) {
				sqls.add(" ALTER TABLE "
						+ tableName
						+ "   ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;");

				sqls.add(" ALTER TABLE "
						+ tableName
						+ "   ENGINE=INNODB DEFAULT CHARSET  utf8 COLLATE utf8_general_ci;");
			}

			sql = "SELECT column_name,column_type,collation_name,column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='"
					+ dbName + "' AND  TABLE_NAME='" + tableName + "'  ";

			List<Map<String, Object>> columns = jdbctemplate.queryForList(sql);

			for (Map<String, Object> c : columns) {

				String cName = (String) c.get("column_name");

				String collName = (String) c.get("collation_name");

				String columnType = (String) c.get("column_type");

				if (StringUtils.isNotEmpty(collName)) {

					if ("utf8_bin".equals(collName)
							|| "utf8_unicode_ci".equals(collName)) {
						String cc = "ALTER TABLE "
								+ tableName
								+ " CHANGE "
								+ cName
								+ " "
								+ cName
								+ " "
								+ columnType
								+ " CHARACTER SET utf8 COLLATE utf8_general_ci;";

						sqls2.add(cc);
					}

				}

			}

		}

		StopWatch st = new StopWatch();
		JdbcTemplate jdbctemplate3 = getJdbctemplate();

		System.out.println("sql1:" + sqls.size() + "---sql2:" + sqls2.size());

		for (String s : sqls) {
			System.out.println(s);

		}

		for (String s : sqls2) {
			System.out.println(s);

		}

	}

	private JdbcTemplate getJdbctemplate() {
		String url = "jdbc:mysql://" + host + ":3306/" + dbName
				+ "?useUnicode=true&amp;characterEncoding=UTF-8";
		String username = "root";
		String password = "root";

		// tplPath =
		// "E:/Workspace/JavaProject/MyEclipse/isperp/target/test-classes/itsm/isperp/codegenerator/entity.tpl";

		DataSourcePool.init(url, username, password);
		DataSourcePool ds = DataSourcePool.getInstance();

		JdbcTemplate jdbctemplate = new JdbcTemplate(ds);
		return jdbctemplate;
	}

}
